#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3
import os
from datetime import datetime, date
from pathlib import Path

class DatabaseManager:
    """数据库管理器，负责应用程序数据的存储和检索"""
    
    def __init__(self, db_file=None):
        """初始化数据库管理器
        
        Args:
            db_file: 数据库文件路径，如果为None则使用默认路径
        """
        if db_file is None:
            # 使用用户目录下的数据库文件
            self.db_dir = Path.home() / ".bunny-break"
            self.db_file = self.db_dir / "bunny_break.db"
        else:
            self.db_file = Path(db_file)
            self.db_dir = self.db_file.parent
        
        # 确保数据库目录存在
        self.db_dir.mkdir(parents=True, exist_ok=True)
        
        # 连接数据库
        self.conn = None
        self.connect()
    
    def connect(self):
        """连接到数据库"""
        try:
            self.conn = sqlite3.connect(str(self.db_file))
            self.conn.row_factory = sqlite3.Row  # 使查询结果可以通过列名访问
        except sqlite3.Error as e:
            print(f"连接数据库失败: {e}")
    
    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
    
    def init_db(self):
        """初始化数据库表结构"""
        if not self.conn:
            self.connect()
        
        try:
            cursor = self.conn.cursor()
            
            # 创建工作记录表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS work_sessions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                start_time TIMESTAMP NOT NULL,
                end_time TIMESTAMP,
                duration INTEGER,  -- 持续时间（秒）
                mode TEXT NOT NULL,  -- pomodoro 或 flowdoro
                completed BOOLEAN DEFAULT 0,  -- 是否完成
                notes TEXT
            )
            ''')
            
            # 创建休息记录表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS break_sessions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                start_time TIMESTAMP NOT NULL,
                end_time TIMESTAMP,
                duration INTEGER,  -- 持续时间（秒）
                work_session_id INTEGER,  -- 关联的工作记录ID
                completed BOOLEAN DEFAULT 0,  -- 是否完成
                FOREIGN KEY (work_session_id) REFERENCES work_sessions (id)
            )
            ''')
            
            # 创建每日统计表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS daily_stats (
                date TEXT PRIMARY KEY,  -- YYYY-MM-DD 格式
                total_work_seconds INTEGER DEFAULT 0,  -- 总工作时间（秒）
                total_break_seconds INTEGER DEFAULT 0,  -- 总休息时间（秒）
                completed_pomodoros INTEGER DEFAULT 0,  -- 完成的番茄钟数量
                completed_flowdoros INTEGER DEFAULT 0,  -- 完成的Flowdoro数量
                longest_work_session INTEGER DEFAULT 0  -- 最长工作时间（秒）
            )
            ''')
            
            self.conn.commit()
        except sqlite3.Error as e:
            print(f"初始化数据库失败: {e}")
            self.conn.rollback()
    
    def start_work_session(self, mode):
        """开始一个工作会话
        
        Args:
            mode: 工作模式，'pomodoro' 或 'flowdoro'
            
        Returns:
            新创建的工作会话ID
        """
        try:
            cursor = self.conn.cursor()
            now = datetime.now()
            cursor.execute(
                "INSERT INTO work_sessions (start_time, mode) VALUES (?, ?)",
                (now, mode)
            )
            self.conn.commit()
            return cursor.lastrowid
        except sqlite3.Error as e:
            print(f"创建工作会话失败: {e}")
            self.conn.rollback()
            return None
    
    def end_work_session(self, session_id, completed=True, notes=None):
        """结束一个工作会话
        
        Args:
            session_id: 工作会话ID
            completed: 是否完成
            notes: 备注
            
        Returns:
            更新是否成功
        """
        try:
            cursor = self.conn.cursor()
            now = datetime.now()
            
            # 获取会话开始时间
            cursor.execute(
                "SELECT start_time, mode FROM work_sessions WHERE id = ?",
                (session_id,)
            )
            row = cursor.fetchone()
            if not row:
                return False
            
            start_time = datetime.fromisoformat(row['start_time'])
            mode = row['mode']
            duration = int((now - start_time).total_seconds())
            
            # 更新工作会话
            cursor.execute(
                "UPDATE work_sessions SET end_time = ?, duration = ?, completed = ?, notes = ? WHERE id = ?",
                (now, duration, completed, notes, session_id)
            )
            
            # 更新每日统计
            today = date.today().isoformat()
            cursor.execute(
                "INSERT OR IGNORE INTO daily_stats (date) VALUES (?)",
                (today,)
            )
            
            if completed:
                if mode == 'pomodoro':
                    cursor.execute(
                        "UPDATE daily_stats SET total_work_seconds = total_work_seconds + ?, completed_pomodoros = completed_pomodoros + 1 WHERE date = ?",
                        (duration, today)
                    )
                else:  # flowdoro
                    cursor.execute(
                        "UPDATE daily_stats SET total_work_seconds = total_work_seconds + ?, completed_flowdoros = completed_flowdoros + 1 WHERE date = ?",
                        (duration, today)
                    )
            else:
                cursor.execute(
                    "UPDATE daily_stats SET total_work_seconds = total_work_seconds + ? WHERE date = ?",
                    (duration, today)
                )
            
            # 更新最长工作时间
            cursor.execute(
                "UPDATE daily_stats SET longest_work_session = MAX(longest_work_session, ?) WHERE date = ?",
                (duration, today)
            )
            
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"结束工作会话失败: {e}")
            self.conn.rollback()
            return False
    
    def start_break_session(self, work_session_id):
        """开始一个休息会话
        
        Args:
            work_session_id: 关联的工作会话ID
            
        Returns:
            新创建的休息会话ID
        """
        try:
            cursor = self.conn.cursor()
            now = datetime.now()
            cursor.execute(
                "INSERT INTO break_sessions (start_time, work_session_id) VALUES (?, ?)",
                (now, work_session_id)
            )
            self.conn.commit()
            return cursor.lastrowid
        except sqlite3.Error as e:
            print(f"创建休息会话失败: {e}")
            self.conn.rollback()
            return None
    
    def end_break_session(self, session_id, completed=True):
        """结束一个休息会话
        
        Args:
            session_id: 休息会话ID
            completed: 是否完成
            
        Returns:
            更新是否成功
        """
        try:
            cursor = self.conn.cursor()
            now = datetime.now()
            
            # 获取会话开始时间
            cursor.execute(
                "SELECT start_time FROM break_sessions WHERE id = ?",
                (session_id,)
            )
            row = cursor.fetchone()
            if not row:
                return False
            
            start_time = datetime.fromisoformat(row['start_time'])
            duration = int((now - start_time).total_seconds())
            
            # 更新休息会话
            cursor.execute(
                "UPDATE break_sessions SET end_time = ?, duration = ?, completed = ? WHERE id = ?",
                (now, duration, completed, session_id)
            )
            
            # 更新每日统计
            if completed:
                today = date.today().isoformat()
                cursor.execute(
                    "UPDATE daily_stats SET total_break_seconds = total_break_seconds + ? WHERE date = ?",
                    (duration, today)
                )
            
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"结束休息会话失败: {e}")
            self.conn.rollback()
            return False
    
    def get_today_stats(self):
        """获取今日统计数据
        
        Returns:
            今日统计数据字典
        """
        try:
            cursor = self.conn.cursor()
            today = date.today().isoformat()
            
            cursor.execute(
                "SELECT * FROM daily_stats WHERE date = ?",
                (today,)
            )
            row = cursor.fetchone()
            
            if row:
                return dict(row)
            else:
                # 如果今天还没有记录，创建一个空记录
                cursor.execute(
                    "INSERT OR IGNORE INTO daily_stats (date) VALUES (?)",
                    (today,)
                )
                self.conn.commit()
                return {
                    'date': today,
                    'total_work_seconds': 0,
                    'total_break_seconds': 0,
                    'completed_pomodoros': 0,
                    'completed_flowdoros': 0,
                    'longest_work_session': 0
                }
        except sqlite3.Error as e:
            print(f"获取今日统计失败: {e}")
            return {
                'date': date.today().isoformat(),
                'total_work_seconds': 0,
                'total_break_seconds': 0,
                'completed_pomodoros': 0,
                'completed_flowdoros': 0,
                'longest_work_session': 0
            } 